﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Windows.Forms;
using CacheProvider;
using Microsoft.Practices.Composite.Presentation.Events;
using CacheProviderEntities;

namespace WindowsFormsClientApplication
{
    public partial class CacheTestForm : Form
    {
        public CacheTestForm()
        {
            InitializeComponent();
        }

        private void ButtonGetProductData_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.WaitCursor;

            const string key = "SqlDependencyCache";
            var data = CacheBroker.Get(key) as DataSet;
            if (data == null)
            {
                Label1.Text = "Reading From Database";

                data = GetDataFromDB();
                var dependencyInfo = new DependencyInfo(CacheDependencyTypes.SQLDependency)
                {
                    DBName = "AdventureWorks",
                    DBConnectionString = GetConnectionString(),
                    IsSQLSelectQueryBased = true
                };

                dependencyInfo.SelectQueries.Add(GetSQL());

                CacheBroker.Put(key, data, string.Empty, TimeSpan.Zero, null, dependencyInfo, HydrateCache, key, ThreadOption.UIThread, true);

                Label1.Text = "Done!Read From Database";
            }
            else
            {
                Label1.Text = "Done! Read From Cache";
            }

            DataGridViewProducts.DataSource = data.Tables[0];

            DataGridViewProducts.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);

            this.Cursor = Cursors.Default;
        }


        private void HydrateCache(object state)
        {
            Label1.Text = "Dependency Changed! Updating Grid";

            this.Cursor = Cursors.WaitCursor;

            var key = state as string;

            Debug.Assert(!string.IsNullOrEmpty(key));

            var data = GetDataFromDB();
            var dependencyInfo = new DependencyInfo(CacheDependencyTypes.SQLDependency)
            {
                DBName = "AdventureWorks",
                DBConnectionString = GetConnectionString(),
                IsSQLSelectQueryBased = true
            };

            dependencyInfo.SelectQueries.Add(GetSQL());

            CacheBroker.Put<object>(key, data, string.Empty, TimeSpan.Zero, null, dependencyInfo, HydrateCache, key, ThreadOption.UIThread, true);

            DataGridViewProducts.DataSource = data.Tables[0];

            DataGridViewProducts.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);

            Label1.Text = "Grid Updated";
            this.Cursor = Cursors.Default;
        }

        private static DataSet GetDataFromDB()
        {
            DataSet dataset;

            var sqlText = GetSQL();

            using (var connection =
               new SqlConnection(GetConnectionString()))
            {
                using (var command =
                    new SqlCommand(sqlText, connection))
                {
                    connection.Open();
                    dataset = new DataSet();
                    new SqlDataAdapter(command).Fill(dataset);
                }
            }
            return dataset;
        }

        private static string GetConnectionString()
        {
            // To avoid storing the connection string in your code,
            // you can retrieve it from a configuration file.
            if (Environment.MachineName.Equals("IN8202039D"))
                return @"Data Source=SRVBLRCAF01\MSBLRTCTD01;Integrated Security=true;" +
                  "Initial Catalog=AdventureWorks;";

            return @"Data Source=RAHUL-PC\SQL2008EXPRESS;Integrated Security=true;" +
              "Initial Catalog=AdventureWorks;";
        }

        private static string GetSQL()
        {
            return "SELECT Production.Product.ProductID, " +
            "Production.Product.Name, " +
            "Production.Location.Name AS Location, " +
            "Production.ProductInventory.Quantity " +
            "FROM Production.Product INNER JOIN " +
            "Production.ProductInventory " +
            "ON Production.Product.ProductID = " +
            "Production.ProductInventory.ProductID " +
            "INNER JOIN Production.Location " +
            "ON Production.ProductInventory.LocationID = " +
            "Production.Location.LocationID " +
            "WHERE ( Production.ProductInventory.Quantity <= 100 ) " +
            "ORDER BY Production.ProductInventory.Quantity, " +
            "Production.Product.Name;";
        }
    }
}
